Using Comparison Operators in MySQL

Course: MySQL Training

Course Link: MySQL Training Course – JustAcademy

Demo Link: Register for a Course Demo

1. Introduction to Comparison Operators in MySQL

Comparison operators in MySQL are used to compare two values or expressions. They help determine whether a value is equal to, greater than, less than, or different from another value. These operators are commonly used in SQL queries to filter records, retrieve specific information, update selected rows, and delete records that meet particular conditions.

For example, a college database may contain students with different marks. If you want to retrieve students who scored more than 80 marks, you can use the greater-than operator (>) in the WHERE clause.

SELECT *
FROM students
WHERE marks > 80;

This query returns records where the marks value is greater than 80. Students who scored exactly 80 are not included.

Comparison operators are important because databases often contain thousands or millions of records. Instead of retrieving every row, you can use conditions to select only the records relevant to your task.

Why Are Comparison Operators Important?

• Filter records using specific conditions.
• Compare numerical values such as marks, salaries, prices, and quantities.
• Compare text values such as names, courses, and statuses.
• Identify records that fall within or outside a specified range.
• Combine multiple conditions using logical operators.
• Control which records are affected by UPDATE and DELETE statements.
• Support reporting, searching, validation, and data analysis.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand the meaning and purpose of comparison operators.
• Use the equal-to and not-equal-to operators.
• Compare values using greater-than and less-than operators.
• Use greater-than-or-equal-to and less-than-or-equal-to conditions.
• Filter records using WHERE.
• Combine comparisons with AND, OR, and NOT.
• Use BETWEEN, IN, and LIKE for specialized comparisons.
• Handle NULL values correctly.
• Understand comparison results and common type-conversion issues.
• Apply comparison operators in practical MySQL queries.

3. What Is a Comparison Operator?

A comparison operator evaluates the relationship between two values. In MySQL, comparison expressions commonly return 1 for TRUE, 0 for FALSE, or NULL when the result is unknown, such as in many comparisons involving NULL.

Example

SELECT 10 > 5 AS result;

Result: 1, because 10 is greater than 5.

SELECT 10 < 5 AS result;

Result: 0, because 10 is not less than 5.

SELECT 10 = 10 AS result;

Result: 1, because both values are equal.

Comparison expressions can be used directly in a SELECT statement or inside conditions in queries such as SELECT, UPDATE, and DELETE.

4. Main Comparison Operators in MySQL

These operators form the foundation of filtering conditions in MySQL. Additional comparison-related operators and predicates, including BETWEEN, IN, LIKE, IS NULL, and IS NOT NULL, help handle common types of comparisons.

5. Preparing a Sample Database

Before practising the comparison operators, create a sample database and a table containing student records.

Step 1: Create a Database

CREATE DATABASE college_db;
USE college_db;

Step 2: Create the Students Table

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    course VARCHAR(50),
    marks DECIMAL(5,2),
    age INT,
    city VARCHAR(50),
    email VARCHAR(100),
    status VARCHAR(20)
);

Step 3: Insert Sample Records

INSERT INTO students
(student_id, student_name, course, marks, age, city, email, status)
VALUES
(1, 'Rahul Sharma', 'MySQL', 78.50, 22, 'Mumbai', '[email protected]', 'Active'),
(2, 'Priya Patel', 'Python', 85.00, 23, 'Pune', '[email protected]', 'Active'),
(3, 'Amit Verma', 'Java', 69.50, 21, 'Delhi', '[email protected]', 'Active'),
(4, 'Sneha Singh', 'MySQL', 92.00, 24, 'Mumbai', '[email protected]', 'Active'),
(5, 'Karan Mehta', 'Python', 55.00, 22, 'Nashik', '[email protected]', 'Inactive'),
(6, 'Neha Joshi', 'Java', 88.00, 23, 'Pune', NULL, 'Active'),
(7, 'Rohit Shah', 'SQL', 73.00, 20, 'Mumbai', '[email protected]', 'Inactive'),
(8, 'Meera Nair', 'MySQL', 90.00, 25, 'Delhi', '[email protected]', 'Active');

Step 4: View the Data

SELECT * FROM students;

This table will be used throughout the following examples.

6. Equal-to Operator (=)

The equal-to operator (=) checks whether two values are equal. It is commonly used to find records with a specific ID, city, course, status, or numerical value.

Syntax

SELECT column_name
FROM table_name
WHERE column_name = value;

Example 1: Find Students Enrolled in MySQL

SELECT *
FROM students
WHERE course = 'MySQL';

This query returns students whose course value equals MySQL.

Example 2: Find Students from Mumbai

SELECT student_name, city
FROM students
WHERE city = 'Mumbai';

Only records with a matching city value are returned.

Example 3: Find a Student by ID

SELECT *
FROM students
WHERE student_id = 4;

This retrieves the record for student ID 4. Since student_id is the primary key, the query can match at most one record.

Example 4: Find Students with Exactly 90 Marks

SELECT student_name, marks
FROM students
WHERE marks = 90;

This returns students whose stored marks value equals 90.00.

7. Not-equal Operators (!= and <>)

The not-equal operators identify values that are different from a specified value. MySQL supports both != and <> for not-equal comparisons.

Example 1: Find Students Who Are Not Enrolled in MySQL

SELECT student_name, course
FROM students
WHERE course != 'MySQL';

This query returns records whose course value is not equal to MySQL.

Example 2: Use the Alternative Operator

SELECT student_name, city
FROM students
WHERE city <> 'Mumbai';

This returns students whose city value is different from Mumbai.

Example 3: Find Students Who Are Not Active

SELECT student_name, status
FROM students
WHERE status != 'Active';

This returns records with a status value different from Active. A NULL status does not match this condition because the comparison result is unknown rather than TRUE.

Important Note

!= and <> are equivalent in MySQL. When working with NULL values, use IS NULL or IS NOT NULL rather than relying on not-equal comparisons.

8. Greater-than Operator (>)

The greater-than operator checks whether the left value is strictly greater than the right value.

Example 1: Find Students with Marks Above 80

SELECT student_name, marks
FROM students
WHERE marks > 80;

Students with marks of 85, 88, 90, and 92 satisfy this condition in the sample data. A student with exactly 80 marks would not be included.

Example 2: Find Students Older Than 22

SELECT student_name, age
FROM students
WHERE age > 22;

This returns students whose age is greater than 22.

Example 3: Find Records with Marks Greater Than 70

SELECT *
FROM students
WHERE marks > 70;

This is useful when you want to filter records according to a minimum performance threshold.

9. Less-than Operator (<)

The less-than operator checks whether the left value is strictly smaller than the right value.

Example 1: Find Students with Marks Below 60

SELECT student_name, marks
FROM students
WHERE marks < 60;

This returns students whose marks are less than 60. Students with exactly 60 marks are excluded.

Example 2: Find Students Younger Than 22

SELECT student_name, age
FROM students
WHERE age < 22;

This query identifies students whose age is below 22.

Example 3: Find Students with Marks Below 75

SELECT student_id, student_name, marks
FROM students
WHERE marks < 75;

The condition is evaluated for each row, and only records satisfying it are returned.

10. Greater-than-or-equal Operator (>=)

The greater-than-or-equal operator checks whether a value is greater than or equal to another value. It includes records that exactly match the specified threshold.

Example 1: Find Students with Marks of at Least 80

SELECT student_name, marks
FROM students
WHERE marks >= 80;

This query includes students with marks of 80 or higher.

Example 2: Find Students Aged 23 or Older

SELECT student_name, age
FROM students
WHERE age >= 23;

This returns students whose age is 23 or greater.

Example 3: Find Students with Marks of 90 or Above

SELECT *
FROM students
WHERE marks >= 90;

Unlike marks > 90, this query includes students whose marks are exactly 90.

11. Less-than-or-equal Operator (<=)

The less-than-or-equal operator checks whether a value is smaller than or equal to another value.

Example 1: Find Students with Marks of 60 or Below

SELECT student_name, marks
FROM students
WHERE marks <= 60;

This includes students with exactly 60 marks as well as students with lower marks.

Example 2: Find Students Aged 21 or Younger

SELECT student_name, age
FROM students
WHERE age <= 21;

This query returns students whose age is 21 or less.

Example 3: Find Students with Marks at Most 75

SELECT student_name, marks
FROM students
WHERE marks <= 75;

This is useful when filtering values that must not exceed a defined limit.

12. Comparison Operators: Complete Numerical Example

The following table summarizes the behaviour of the numerical comparison operators using the value 80.

13. Using BETWEEN for Range Comparisons

The BETWEEN operator checks whether a value falls within a range. The lower and upper boundaries are included.

Syntax

SELECT column_name
FROM table_name
WHERE column_name BETWEEN lower_value AND upper_value;

Example 1: Find Students with Marks Between 70 and 90

SELECT student_name, marks
FROM students
WHERE marks BETWEEN 70 AND 90;

This includes marks equal to 70 and 90, along with values between them.

Example 2: Find Students Aged Between 21 and 23

SELECT student_name, age
FROM students
WHERE age BETWEEN 21 AND 23;

Example 3: Use NOT BETWEEN

SELECT student_name, marks
FROM students
WHERE marks NOT BETWEEN 60 AND 80;

This returns students whose marks are outside the inclusive range from 60 to 80.

Equivalent Conditions

SELECT student_name, marks
FROM students
WHERE marks >= 70
  AND marks <= 90;

For values of the same compatible type, this expresses the same inclusive range as BETWEEN 70 AND 90.

14. Using IN and NOT IN

The IN operator checks whether a value matches any value in a specified list. It is often simpler than writing several equality conditions joined by OR.

Example 1: Find Students in Selected Courses

SELECT student_name, course
FROM students
WHERE course IN ('MySQL', 'Python');

This returns students enrolled in either MySQL or Python.

Example 2: Equivalent OR Conditions

SELECT student_name, course
FROM students
WHERE course = 'MySQL'
   OR course = 'Python';

The above query expresses the same basic selection as the IN example.

Example 3: Use NOT IN

SELECT student_name, course
FROM students
WHERE course NOT IN ('MySQL', 'Python');

This excludes records whose course is MySQL or Python. Be careful when using NOT IN with a list or subquery that can contain NULL, because the result may become unknown for values that do not match.

15. Using LIKE and NOT LIKE

The LIKE operator compares text values against a pattern. It is useful when you know only part of a name, email address, city, or other text field.

Two common wildcard characters are:

• % matches zero or more characters.
• _ matches exactly one character.

Example 1: Find Names Starting with R

SELECT student_name
FROM students
WHERE student_name LIKE 'R%';

The pattern 'R%' matches names that begin with R, subject to the column's collation and case-sensitivity rules.

Example 2: Find Names Ending with a

SELECT student_name
FROM students
WHERE student_name LIKE '%a';

The pattern matches names whose final character is a.

Example 3: Find Names Containing the Text 'ha'

SELECT student_name
FROM students
WHERE student_name LIKE '%ha%';

The percent signs allow any number of characters before and after the specified text.

Example 4: Use NOT LIKE

SELECT student_name
FROM students
WHERE student_name NOT LIKE 'R%';

This returns names that do not match the pattern. NULL values do not satisfy a NOT LIKE condition.

16. Comparing NULL Values

In MySQL, NULL represents a missing or unknown value. It is not the same as zero, an empty string, or the word 'NULL'.

Ordinary comparisons involving NULL generally return NULL rather than TRUE or FALSE. Therefore, use IS NULL and IS NOT NULL to test whether a column contains a missing value.

Incorrect Comparison

SELECT *
FROM students
WHERE email = NULL;

This does not correctly identify rows with NULL email values.

Correct Comparison: IS NULL

SELECT student_name, email
FROM students
WHERE email IS NULL;

This returns records whose email field is NULL.

Find Records with a Non-NULL Email

SELECT student_name, email
FROM students
WHERE email IS NOT NULL;

This returns records whose email field contains a non-NULL value. It does not guarantee that the email address is valid or verified.

NULL-Safe Equality Operator (<=>)

MySQL provides the <=> operator for NULL-safe equality comparison. Unlike =, it returns TRUE when both operands are NULL and FALSE when only one operand is NULL.

SELECT
    NULL = NULL AS ordinary_equality,
    NULL <=> NULL AS null_safe_equality,
    10 <=> NULL AS value_vs_null;

The ordinary equality comparison returns NULL. The NULL-safe comparison of two NULL values returns 1, and comparing 10 with NULL using <=> returns 0.

17. Comparing Text Values

Comparison operators can be used with text columns such as names, cities, courses, and statuses. Text comparisons depend on the column's character set and collation, which determine how strings are compared and whether differences such as letter case matter.

Example 1: Compare Course Names

SELECT student_name, course
FROM students
WHERE course = 'Java';

Example 2: Exclude a City

SELECT student_name, city
FROM students
WHERE city <> 'Pune';

Example 3: Check a Specific Status

SELECT student_name, status
FROM students
WHERE status = 'Active';

Important Considerations

• String comparisons may be case-insensitive or case-sensitive depending on the collation.
• Leading and trailing spaces can affect comparisons according to the data type and collation rules.
• Use consistent data types and formats when comparing values.
• For exact case-sensitive comparisons, select an appropriate collation or use a suitable binary comparison when required.

18. Combining Comparison Operators with AND and OR

Comparison conditions can be combined with logical operators to build more precise filters.

Using AND

AND requires all conditions to be TRUE for a row to satisfy the combined condition.

SELECT student_name, course, marks
FROM students
WHERE course = 'MySQL'
  AND marks > 80;

This returns students enrolled in MySQL whose marks are greater than 80.

Using OR

OR requires at least one condition to be TRUE.

SELECT student_name, city
FROM students
WHERE city = 'Mumbai'
   OR city = 'Pune';

This returns students who live in Mumbai or Pune.

Using NOT

NOT reverses a condition's truth value.

SELECT student_name, status
FROM students
WHERE NOT (status = 'Active');

This returns rows for which the condition inside the parentheses is not TRUE. A NULL status does not become a matching row simply because NOT is used.

Combining Conditions with Parentheses

SELECT student_name, course, marks
FROM students
WHERE (course = 'MySQL' OR course = 'Python')
  AND marks >= 75;

Parentheses clarify that the course condition is evaluated as a group before the marks requirement is applied.

19. Comparison Operators with UPDATE

Comparison operators are useful with UPDATE when only records meeting a specific condition should be modified. Always inspect the target records before running an update that affects important data.

Example 1: Update Students with Low Marks

UPDATE students
SET status = 'Needs Improvement'
WHERE marks < 60;

This changes the status of students whose marks are below 60.

Example 2: Update Students with High Marks

UPDATE students
SET status = 'Excellent'
WHERE marks >= 90;

This changes the status of students with marks of 90 or above.

Example 3: Update Students from a Particular City

UPDATE students
SET status = 'City Verified'
WHERE city = 'Mumbai';

This updates every row whose city is Mumbai. Check the matching records first if the change should apply only to selected students.

Preview the Rows Before Updating

SELECT student_id, student_name, marks, status
FROM students
WHERE marks < 60;

Running a SELECT query with the same condition helps you verify which records the update is intended to affect.

20. Comparison Operators with DELETE

Comparison operators can also be used with DELETE to remove records matching a condition. Because deletion can permanently remove data, review the target records carefully and use appropriate backups or transactions where available.

Example: Find Inactive Students Before Deletion

SELECT *
FROM students
WHERE status = 'Inactive';

Example: Delete Records Below a Marks Threshold

DELETE FROM students
WHERE marks < 40;

This deletes every record whose marks are below 40. It is an illustrative example; do not execute it on important data unless deleting those records is explicitly intended.

Safe Deletion Checklist

• Run SELECT with the same WHERE condition first.
• Confirm the number and identity of the records that match.
• Check for foreign-key relationships and application requirements.
• Use a transaction where supported and appropriate.
• Maintain a recovery or backup strategy for important data.

21. Common Errors and How to Avoid Them

22. Best Practices for Using Comparison Operators

• Use the correct operator: Understand whether the boundary value should be included in the result.
• Use appropriate data types: Store numbers as numeric values, dates as date/time values, and text in suitable character columns.
• Handle NULL explicitly: Use IS NULL, IS NOT NULL, or NULL-safe equality when required.
• Use parentheses for complex logic: This makes combined AND and OR conditions easier to read and reduces mistakes.
• Preview records before modification: Always check the target rows before running UPDATE or DELETE statements.
• Be careful with implicit conversion: MySQL can convert values during comparisons, sometimes producing results that are not intuitive.
• Check string comparison rules: Case sensitivity depends on the character set and collation in use.
• Use indexes where appropriate: Indexes may improve filtering performance depending on the query and execution plan.
• Review complex queries with EXPLAIN: This can help you understand how MySQL intends to execute a SELECT query.
• Keep conditions readable: Use meaningful indentation and consistent formatting.

23. Practical Project: Student Performance Analysis

Use the sample students table to practise comparison operators and prepare a simple student performance report.

Task 1: Find Students Who Scored Above 80

SELECT student_name, marks
FROM students
WHERE marks > 80
ORDER BY marks DESC;

Goal: Identify students who scored more than 80 marks and list the highest scores first.

Task 2: Find Students Who Need Improvement

SELECT student_name, marks
FROM students
WHERE marks < 60;

Goal: Find students whose marks are below 60.

Task 3: Find Students Within a Marks Range

SELECT student_name, marks
FROM students
WHERE marks BETWEEN 70 AND 90
ORDER BY marks DESC;

Goal: Retrieve students whose marks are between 70 and 90, including both endpoints.

Task 4: Find Students from Selected Cities

SELECT student_name, city
FROM students
WHERE city IN ('Mumbai', 'Pune');

Goal: Retrieve students living in either Mumbai or Pune.

Task 5: Find Students Without an Email Address

SELECT student_name, email
FROM students
WHERE email IS NULL;

Goal: Identify records where the email value is missing.

Task 6: Combine Course and Marks Conditions

SELECT student_name, course, marks
FROM students
WHERE (course = 'MySQL' OR course = 'Python')
  AND marks >= 75;

Goal: Find students taking MySQL or Python who have marks of at least 75.

Task 7: Write a Short Report

1. List students with marks of 90 or higher.
2. List students with marks below 70.
3. List students whose ages are between 21 and 23.
4. List students whose city is not Mumbai.
5. Count students whose marks are greater than or equal to 80.
6. Explain how changing a strict comparison to an inclusive comparison changes the result.

24. Frequently Asked Interview Questions

Q1. What are comparison operators in MySQL?

Comparison operators evaluate relationships between values, such as equality, inequality, greater than, and less than. They are often used in WHERE conditions to filter records.

Q2. What is the difference between = and !=?

= checks whether two values are equal, while != checks whether they are not equal. MySQL also supports <> as a not-equal operator.

Q3. What is the difference between > and >=?

> excludes values equal to the specified threshold. >= includes values equal to the threshold.

Q4. What is the difference between < and <=?

< selects values strictly below a threshold. <= also includes values equal to the threshold.

Q5. What is BETWEEN used for?

BETWEEN checks whether a value falls within an inclusive range.

Q6. What is the purpose of IN?

IN checks whether a value matches one of the values in a list or a suitable subquery result.

Q7. Why should we not use = NULL?

Ordinary equality comparisons with NULL produce an unknown result. Use IS NULL to identify NULL values.

Q8. What is the NULL-safe equality operator?

<=> compares values while treating two NULL values as equal for the comparison.

Q9. Can comparison operators be used with UPDATE?

Yes. They can be used in the WHERE clause to specify which rows should be modified.

Q10. Can comparison operators be used with DELETE?

Yes. They can select records for deletion. Because DELETE removes rows, inspect the matching records first.

Q11. What do comparison expressions return in MySQL?

Comparison expressions commonly return 1 for TRUE, 0 for FALSE, or NULL when the result is unknown.

Q12. Why can text comparisons be case-sensitive in some queries?

Text comparison behaviour depends on the column's character set and collation, including whether the collation distinguishes uppercase and lowercase characters.

25. Practice Questions

1. Write a query to find students enrolled in MySQL using the equal-to operator.
2. Write a query to find students who are not enrolled in Python.
3. Write a query to retrieve students whose marks are greater than 75.
4. Write a query to retrieve students whose marks are less than 50.
5. Write a query to retrieve students whose marks are at least 85.
6. Write a query to retrieve students whose age is 22 or below.
7. Write a query to find students whose marks are between 60 and 90.
8. Write a query to find students enrolled in MySQL, Python, or Java.
9. Write a query to find student names that start with S.
10. Write a query to identify students whose email values are NULL.
11. Explain the difference between != and <>.
12. Explain why marks > 80 and marks >= 80 can return different results.
13. Write a query that combines two comparison conditions with AND.
14. Write a query that combines two city conditions with OR.
15. Explain why comparison conditions should be checked before an UPDATE or DELETE statement is executed.

26. Quick Revision

• = checks equality.
• != and <> check inequality.
• > checks whether a value is greater than another value.
• < checks whether a value is smaller than another value.
• >= includes the boundary value.
• <= includes the boundary value.
• BETWEEN checks an inclusive range.
• IN checks membership in a list of values.
• LIKE performs pattern matching.
• IS NULL and IS NOT NULL handle NULL checks.
• <=> performs NULL-safe equality comparison in MySQL.
• AND, OR, and NOT help combine or reverse conditions.
• Comparison expressions commonly return TRUE, FALSE, or NULL.
• Always review WHERE conditions before modifying or deleting records.

27. Official MySQL Documentation

For more information about comparison operators, type conversion, and operator precedence, refer to the official MySQL documentation:

• MySQL 8.4 Reference Manual – Comparison Functions and Operators
• MySQL 8.4 Reference Manual – Operator Precedence
• MySQL 8.4 Reference Manual – Type Conversion in Expression Evaluation

28. Conclusion

Comparison operators are essential for retrieving and managing data in MySQL. They allow you to compare numerical and text values, apply exact conditions, define thresholds, filter ranges, and handle multiple criteria. Operators such as =, !=, >, <, >=, and <= form the foundation of conditional queries, while BETWEEN, IN, and LIKE support more specialized comparisons.

To use comparison operators correctly, understand boundary conditions, choose suitable data types, account for NULL values, and use parentheses when combining conditions. Before running UPDATE or DELETE queries, preview the matching records to prevent unintended changes.

Continue learning: Explore the JustAcademy MySQL Training Course or register for a course demo to learn more about MySQL queries and database management.